package com.hui.dao;

import com.hui.dao.StudentDao;
import com.hui.entity.Student;
import com.hui.util.DruidUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 *
 * @Auther: 李昭辉
 * @Date: 2021/06/20/11:15
 * @Description:
 */
public class StudentDaoImpl implements StudentDao {
    @Override
    public int getCount() {
        Connection con = DruidUtil.getConnection();
        int num = 0;
        try {
            PreparedStatement statement = con.prepareStatement("select count(*) as num from student");
            ResultSet resultSet = statement.executeQuery();
            resultSet.next();
            num = resultSet.getInt("num");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public List<Student> getPageStudent(int start, int pageSize) {
        List<Student> list = new ArrayList<>();
        // 获取连接
        Connection con = DruidUtil.getConnection();
        String sql = "select * from student limit ?,?";
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, start);
            ps.setInt(2, pageSize);
            // 获取结果，遍历结果，并放入List集合中
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String gender = resultSet.getString("gender");
                Date birthday = resultSet.getDate("birthday");
                String email = resultSet.getString("email");
                String desc = resultSet.getString("desc");
                int classId = resultSet.getInt("class_id");
                list.add(new Student(id, name, gender, birthday, email, desc, classId));
            }
            // 关闭资源
            DruidUtil.close(con, ps, resultSet);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Student> getStudentById(int id) {
        List<Student> list = new ArrayList<>();
        // 获取连接
        Connection con = DruidUtil.getConnection();
        String sql = "select * from student where id = ? ";
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            // 获取结果，遍历结果，并放入List集合中
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                int id2 = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String gender = resultSet.getString("gender");
                Date birthday = resultSet.getDate("birthday");
                String email = resultSet.getString("email");
                String desc = resultSet.getString("desc");
                int classId = resultSet.getInt("class_id");
                list.add(new Student(id2, name, gender, birthday, email, desc, classId));
            }
            // 关闭资源
            DruidUtil.close(con, ps, resultSet);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Student> getStudentByClassId(int classId) {
        ArrayList<Student> list = new ArrayList<>();
        Connection con = DruidUtil.getConnection();
        String sql = "SELECT * FROM student WHERE class_id = ?";
        try {
            // 获取预处理对象并设置
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, classId);
            // 执行预处理对象，并获取返回的数据
            ResultSet resultSet = ps.executeQuery();
            // 处理数据，遍历放入list集合中
            while (resultSet.next()) {
                int id2 = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String gender = resultSet.getString("gender");
                Date birthday = resultSet.getDate("birthday");
                String email = resultSet.getString("email");
                String desc = resultSet.getString("desc");
                list.add(new Student(id2, name, gender, birthday, email, desc, classId));
            }
            // 关闭资源
            DruidUtil.close(con, ps, resultSet);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public boolean updateStudent(Student student) {
        Connection con = DruidUtil.getConnection();
        String sql = "update student set name=?,gender=?,birthday=?,email=?,`desc`=?,class_id=? where id=? ";
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, student.getName());
            ps.setString(2, student.getGender());
            // java中的日期转换成sql中的日期，不能强转
            Date date = new Date(student.getBirthday().getTime());
            ps.setDate(3, date);
            ps.setString(4, student.getEmail());
            ps.setString(5, student.getDesc());
            ps.setInt(6, student.getClassId());
            ps.setInt(7, student.getId());
            // 获取结果
            int i = ps.executeUpdate();
            // 关闭资源
            DruidUtil.close(con, ps);
            return i > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean insertStudent(Student student) {
        Connection con = DruidUtil.getConnection();
        String sql = "insert into student values(null,?,?,?,?,?,?) ";
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setString(1, student.getName());
            ps.setString(2, student.getGender());
            // java中的日期转换成sql中的日期，不能强转
            Date date = new Date(student.getBirthday().getTime());
            ps.setDate(3, date);
            ps.setString(4, student.getEmail());
            ps.setString(5, student.getDesc());
            ps.setInt(6, student.getClassId());
            // 获取结果
            int i = ps.executeUpdate();
            // 关闭资源
            DruidUtil.close(con, ps);
            return i > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    @Override
    public boolean deleteStudentById(int id) {
        Connection con = DruidUtil.getConnection();
        String sql = "DELETE FROM student WHERE id = ? ";
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.setInt(1, id);
            // 获取结果
            int i = ps.executeUpdate();
            // 关闭资源
            DruidUtil.close(con, ps);
            return i > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }


}
